﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
   public class TaiKhoanVayDA
   {
       public static TaiKhoanVay GetByID(int maID)
       {
           TaiKhoanVay loai = null;
           SqlConnection cn = DataProvider.Connect();
           SqlCommand cmd = new SqlCommand("Proc_TaiKhoanVay_GetById", cn);
           cmd.CommandType = CommandType.StoredProcedure;
           cmd.Parameters.Add(new SqlParameter("@maTKVay", maID));
           SqlDataReader dataReader = cmd.ExecuteReader();
           if (dataReader.Read())
           {
               loai = new TaiKhoanVay();
               loai.MaTKVay = maID;
               loai.MaTKChinh = Convert.ToInt32(dataReader["MaTKChinh"]);
               loai.MaLSVay = Convert.ToInt32(dataReader["MaLSVay"]);
               loai.MaLoaiTK = Convert.ToInt32(dataReader["MaLoaiTK"]);
               loai.MaLoaiTien = Convert.ToInt32(dataReader["MaLoaiTien"]);
               loai.DelFlg = Convert.ToInt32(dataReader["DelFlg"]);
               loai.NoiDungVay = Convert.ToString(dataReader["NoiDungVay"]);
               loai.SoTienVay = Convert.ToSingle(dataReader["SoTienVay"]);
               loai.NgayBatDau = Convert.ToDateTime(dataReader["NgayBatDau"]);
               loai.NgayKetThuc = Convert.ToDateTime(dataReader["NgayKetThuc"]);

               loai.Money = LoaiTienDA.GetByID(loai.MaLoaiTien);
               loai.LoaiTK = LoaiTaiKhoanDA.GetByID(loai.MaLoaiTK);
               loai.LSVay = ChiTietLSVayDA.GetByID(loai.MaLSVay);
               loai.TKTheChap = TaiKhoanTCDA.GetByMaTKVay(loai.MaTKVay);
               loai.TKChinh = TaiKhoanChinhDA.GetByID(loai.MaTKChinh);
           }
           cn.Close();
           return loai;
       }

       public static List<TaiKhoanVay> GetByMaLSVay(int maID)
       {
           List<TaiKhoanVay> lst = new List<TaiKhoanVay>();
           TaiKhoanVay loai = null;
           SqlConnection cn = DataProvider.Connect();
           SqlCommand cmd = new SqlCommand("Proc_TaihKhoanVay_GetByMaLSVay", cn);
           cmd.CommandType = CommandType.StoredProcedure;
           cmd.Parameters.Add(new SqlParameter("@maLSVay", maID));
           SqlDataReader dataReader = cmd.ExecuteReader();
           while (dataReader.Read())
           {
               loai = new TaiKhoanVay();
               loai.MaLSVay = maID;
               loai.MaTKChinh = Convert.ToInt32(dataReader["MaTKChinh"]);
               loai.MaTKVay = Convert.ToInt32(dataReader["MaTKVay"]);
               loai.MaLoaiTK = Convert.ToInt32(dataReader["MaLoaiTK"]);
               loai.MaLoaiTien = Convert.ToInt32(dataReader["MaLoaiTien"]);
               loai.DelFlg = Convert.ToInt32(dataReader["DelFlg"]);
               loai.NoiDungVay = Convert.ToString(dataReader["NoiDungVay"]);
               loai.SoTienVay = Convert.ToSingle(dataReader["SoTienVay"]);
               loai.NgayBatDau = Convert.ToDateTime(dataReader["NgayBatDau"]);
               loai.NgayKetThuc = Convert.ToDateTime(dataReader["NgayKetThuc"]);

               loai.Money = LoaiTienDA.GetByID(loai.MaLoaiTien);
               loai.LoaiTK = LoaiTaiKhoanDA.GetByID(loai.MaLoaiTK);
               loai.LSVay = ChiTietLSVayDA.GetByID(loai.MaLSVay);
               loai.TKChinh = TaiKhoanChinhDA.GetByID(loai.MaTKChinh);
               loai.TKTheChap = TaiKhoanTCDA.GetByMaTKVay(loai.MaTKVay);
               lst.Add(loai);
           }
           cn.Close();
           return lst;
       }

       public static List<TaiKhoanVay> GetByMaLoaiTK(int MaLoaiTK)

       {
           List<TaiKhoanVay> lst = new List<TaiKhoanVay>();
           TaiKhoanVay loai = null;
           SqlConnection cn = DataProvider.Connect();
           SqlCommand cmd = new SqlCommand("Proc_TaiKhoanVay_GetByMaLoaiTK");
           cmd.CommandType = CommandType.StoredProcedure;
           cmd.Parameters.Add(new SqlParameter( "@maLoaiTK", MaLoaiTK));
           SqlDataReader dtReader = cmd.ExecuteReader();

           while (dtReader.Read())
           {
               loai = new TaiKhoanVay();
               loai.MaLoaiTK = MaLoaiTK;
               loai.MaLoaiTien = Convert.ToInt32(dtReader["MaLoaiTien"]);
               loai.MaLSVay = Convert.ToInt32(dtReader["MaLSVay"]);
               loai.MaTKChinh = Convert.ToInt32(dtReader["MaTKChinh"]);
               loai.MaTKVay = Convert.ToInt32(dtReader["MaTKVay"]);
               loai.DelFlg = Convert.ToInt32(dtReader["DelFlg"]);
               loai.NoiDungVay = Convert.ToString(dtReader["NoiDungVay"]);
               loai.SoTienVay = Convert.ToSingle(dtReader["SoTienVay"]);
               loai.NgayBatDau = Convert.ToDateTime(dtReader["NgayBatDau"]);
               loai.NgayKetThuc = Convert.ToDateTime(dtReader["NgayKetThuc"]);


               loai.TKTheChap = TaiKhoanTCDA.GetByMaTKVay(loai.MaTKVay);
               loai.Money = LoaiTienDA.GetByID(loai.MaLoaiTien);
               loai.LoaiTK = LoaiTaiKhoanDA.GetByID(loai.MaLoaiTK);
               loai.LSVay = ChiTietLSVayDA.GetByID(loai.MaLSVay);
               loai.TKChinh = TaiKhoanChinhDA.GetByID(loai.MaTKChinh);
               lst.Add(loai);
           }
           cn.Close();
           return lst;
       }
       public static List<TaiKhoanVay> GetByMaLoaiTien(int MaLoaiTien)
       {
           List<TaiKhoanVay> lst = new List<TaiKhoanVay>();
           TaiKhoanVay loai = null;
           SqlConnection cn = DataProvider.Connect();
           SqlCommand cmd = new SqlCommand("Proc_TaiKhoanVay_GetByMaLoaiTien");
           cmd.CommandType = CommandType.StoredProcedure;
           cmd.Parameters.Add(new SqlParameter("@maLoaiTien", MaLoaiTien));
           SqlDataReader dtReader = cmd.ExecuteReader();

           while (dtReader.Read())
           {
               loai = new TaiKhoanVay();
               loai.MaLoaiTien = MaLoaiTien;
               loai.MaLoaiTK = Convert.ToInt32(dtReader["MaLoaiTK"]);
               loai.MaLSVay = Convert.ToInt32(dtReader["MaLSVay"]);
               loai.MaTKChinh = Convert.ToInt32(dtReader["MaTKChinh"]);
               loai.MaTKVay = Convert.ToInt32(dtReader["MaTKVay"]);
               loai.DelFlg = Convert.ToInt32(dtReader["DelFlg"]);
               loai.NoiDungVay = Convert.ToString(dtReader["NoiDungVay"]);
               loai.SoTienVay = Convert.ToSingle(dtReader["SoTienVay"]);
               loai.NgayBatDau = Convert.ToDateTime(dtReader["NgayBatDau"]);
               loai.NgayKetThuc = Convert.ToDateTime(dtReader["NgayKetThuc"]);


               loai.TKTheChap = TaiKhoanTCDA.GetByMaTKVay(loai.MaTKVay);
               loai.Money = LoaiTienDA.GetByID(loai.MaLoaiTien);
               loai.LoaiTK = LoaiTaiKhoanDA.GetByID(loai.MaLoaiTK);
               loai.LSVay = ChiTietLSVayDA.GetByID(loai.MaLSVay);
               loai.TKChinh = TaiKhoanChinhDA.GetByID(loai.MaTKChinh);
               lst.Add(loai);
           }
           cn.Close();
           return lst;
       }
       public static TaiKhoanVay GetByMaTKChinh(int maTKChinh)
       {
           TaiKhoanVay loai = null;
           SqlConnection cn = DataProvider.Connect();
           SqlCommand cmd = new SqlCommand("Proc_TaiKhoanVay_GetByMaTKChinh", cn);
           cmd.CommandType = CommandType.StoredProcedure;
           cmd.Parameters.Add(new SqlParameter("@maTKChinh", maTKChinh));
           SqlDataReader dataReader = cmd.ExecuteReader();
           if (dataReader.Read())
           {
               loai = new TaiKhoanVay();
               loai.MaTKChinh = maTKChinh;
               loai.MaTKVay = Convert.ToInt32(dataReader["MaTKVay"]);
               loai.MaLSVay = Convert.ToInt32(dataReader["MaLSVay"]);
               loai.MaLoaiTK = Convert.ToInt32(dataReader["MaLoaiTK"]);
               loai.MaLoaiTien = Convert.ToInt32(dataReader["MaLoaiTien"]);
               loai.DelFlg = Convert.ToInt32(dataReader["DelFlg"]);
               loai.NoiDungVay = Convert.ToString(dataReader["NoiDungVay"]);
               loai.SoTienVay = Convert.ToSingle(dataReader["SoTienVay"]);
               loai.NgayBatDau = Convert.ToDateTime(dataReader["NgayBatDau"]);
               loai.NgayKetThuc = Convert.ToDateTime(dataReader["NgayKetThuc"]);

               loai.TKTheChap = TaiKhoanTCDA.GetByMaTKVay(loai.MaTKVay);
               loai.Money = LoaiTienDA.GetByID(loai.MaLoaiTien);
               loai.LoaiTK = LoaiTaiKhoanDA.GetByID(loai.MaLoaiTK);
               loai.LSVay = ChiTietLSVayDA.GetByID(loai.MaLSVay);
               loai.TKChinh = TaiKhoanChinhDA.GetByID(loai.MaTKChinh);
           }
           cn.Close();
           return loai;
       }
    }
}
